%%%-------------------------------------------------------------------
%%% @author Rain
%%% @doc mysql数据库操作接口
%%% @end
%%%-------------------------------------------------------------------
-module(db_mysql).
-author("Rain").
-include("db.hrl").

%% API

-export([
    load_db_all/1,
    load_db_where/3
]).

-export([
    select/2,
    select/3,
    select/4,
    select/5,
    select/6
]).

-export([
    insert_update/3
]).

-export([
    delete_where/4
]).

%% DB API 返回的都是mysql_pool:query的返回值
-export([
    db_select/2,
    db_select/3,
    db_select/4,
    db_select/5,
    db_select/6
]).

-export([db_insert/3]).
-export([db_insert_update/3]).
-export([db_delete/3]).


%%%===================================================================
%%% Api Functions
%%%===================================================================

%% @doc 根据Key为Value的where语句筛选出当前db_mod的所有字段的数据
load_db_where(Table, Key, Value) ->
    case select(Table, ?all, [{Key, Value}]) of
        {ok, DataList} ->
            {ok, DataList};
        _ ->
            % 数据库刚开始为空，没有数据，若为报错有日志打点
            pass
    end.

%% @doc 获取某个表的所有数据
load_db_all(Table) ->
    case select(Table, ?all) of
        {ok, DataList} ->
            {ok, DataList};
        _ ->
            % 数据库刚开始为空，没有数据，若为报错有日志打点
            pass
    end.


%% @doc 获取db数据表模块
get_db_mod(Table0) ->
    Table = game_util:to_atom(Table0),
    db_db_config:get_cfg(mod, Table).

%% @doc 将数据从数据库获取出来并转换为对应的记录的列表
%% @usage:
%% db_mysql:select(?DB_TYPE_GAME, rank).
%% db_mysql:select(?DB_TYPE_GAME, rank, ["id"]).
%% db_mysql:select(?DB_TYPE_GAME, rank, ["id"], [{"id", 1}]).
%% db_mysql:select(?DB_TYPE_GAME, rank, ["id"], [{"id", 1}], {"id", "ASC"}).
%% db_mysql:select(?DB_TYPE_GAME, rank, ["id"], [{"id", 1}], {"id", "ASC"}, 2).
%% 简单的where xx = xx的格式可以使用 [{WhereKey, WhereValue}]的形式
%% 复杂的where语句就自己写完整的where语句
%% exam:
%% WhereString = lists:flatten(io_lib:format("where id > ~p", [1])),
%% db_mysql:select(rank, ["id"], WhereString).
%% return: {ok, #template_rec{}|...} | error
select(DBType, Table) ->
    select(DBType, Table, ?all).
select(DBType, Table, Fields) ->
    select(DBType, Table, Fields, "").
select(DBType, Table, Fields, Where) ->
    select(DBType, Table, Fields, Where, []).
select(DBType, Table, Fields, Where, Order) ->
    select(DBType, Table, Fields, Where, Order, 0).
select(DBType, Table, Fields, Where, Order, Limit) ->
    try
        {ok, DbFieldsBin, DbDatasList} = db_select(DBType, Table, Fields, Where, Order, Limit),
        List = db_data_to_rec_list(Table, DbFieldsBin, DbDatasList),
        {ok, List}
    catch
        Class: Reason: Stack ->
            ?LOG_ERROR("===================DB SELECT ERROR ==================~n
            Class ~p Reason ~p~n
            Stack ~p~n
            ", [Class, Reason, Stack]),
            error
    end.

%% @doc 数据库数据转换成记录列表
db_data_to_rec_list(Table, DbFieldsBin, DbDatasList) ->
    DbMod = get_db_mod(Table),
    InitRec = DbMod:mod_db_init_rec(),
    RecFields = DbMod:mod_db_fields(),
    DbFields = [game_util:to_atom(DbFieldBin) || DbFieldBin <- DbFieldsBin],
    db_data_to_rec_list(DbDatasList, DbFields, RecFields, InitRec, [], Table).

db_data_to_rec_list([], _DbFields, _RecFields, _InitRec, RecDataList, _Table) ->
    RecDataList;
db_data_to_rec_list([DbDatas | DbDatasT], DbFields, RecFields, InitRec, RecDataList, Table) ->
    AddRec = db_data_to_rec_list2(DbFields, DbDatas, RecFields, InitRec, Table),
    db_data_to_rec_list(DbDatasT, DbFields, RecFields, InitRec, [AddRec | RecDataList], Table).

db_data_to_rec_list2([], [], _RecFields, Rec, _Table) ->
    Rec;
db_data_to_rec_list2([DbField | DbFieldsT], [DbData | DbDataT], RecFields, Rec, Table) ->
    case lists:keyfind(DbField, 1, RecFields) of
        {DbField, RecIdx, Type} ->
            DbData1 = decode(Type, DbData),
            Rec1 = erlang:setelement(RecIdx, Rec, DbData1);
        _ ->
            ?LOG_ERROR("Table ~p DbField ~p not find in mod_db_fields", [Table, DbField]),
            Rec1 = Rec
    end,
    db_data_to_rec_list2(DbFieldsT, DbDataT, RecFields, Rec1, Table).




%% @usage:
%% db_mysql:insert_update(rank, #rank{}).
%% return: ok | error
insert_update(DBType, Table, Record) when is_tuple(Record) ->
    try
        Data = rec_to_proplists(Table, Record),
        ok = db_insert_update(DBType, Table, Data),
        ok
    catch
        Class: Reason: Stack ->
            ?LOG_ERROR("===================DB INSERT UPDATE ERROR ==================~n
            Class ~p Reason ~p~n
            Stack ~p~n
            ", [Class, Reason, Stack]),
            error
    end.

rec_to_proplists(Table, Record) ->
    DbMod = get_db_mod(Table),
    RecFields = DbMod:mod_db_fields(),
    rec_to_proplists1(RecFields, Record, []).

rec_to_proplists1([], _Record, Acc) ->
    lists:reverse(Acc);
rec_to_proplists1([{DbField, RecIdx, _}|T], Record, Acc) ->
    Data = element(RecIdx, Record),
    rec_to_proplists1(T, Record, [{DbField, Data} | Acc]).


%% db_mysql:delete(rank, #rank{}).
%% return: ok | error
delete_where(DBType, Table, Key, Value) ->
    try
        case db_delete(DBType, Table, [{Key, Value}]) of
            ok ->
                ok;
            {ok, _} ->
                ok
        end
    catch
        Class: Reason: Stack ->
            ?LOG_ERROR("===================DB INSERT UPDATE ERROR ==================~n
            Class ~p Reason ~p~n
            Stack ~p~n
            ", [Class, Reason, Stack]),
            error
    end.

%%%===================================================================
%%% DB Api Functions
%%%===================================================================

-type query_result() :: ok
| {ok, [ColumnName :: term()], [Row :: term()]}
| {ok, [{[ColumnName :: term()], [Row :: term()]}, ...]}
| {error, Reason :: term()}.

-spec db_insert(DBType, Table, Data) -> Result
    when DBType :: db_type(),
    Table :: atom() | string(),
    Data :: [{DataKey :: string() | atom(), DataValue :: string() | number()}],
    Result :: query_result().

%% @usage:
%% db_mysql:db_insert(rank, [{id, 1}, {data, "2"}]).
db_insert(DBType, Table, Data = [_ | _]) ->
    {Sql, Params} = insert_sql_str(Data, Table),
%%    ?INFO("Sql ~s~n Params ~p", [Sql, Params]),
    mysql_pool:query(DBType, Sql, Params).

-spec db_select(DBType, Table | Fields | Where | Order | Limit) -> Result
    when DBType :: db_type(),
    Table :: atom() | string(),
    Fields :: [string() | atom()],
    Where :: [{WhereKey :: string() | atom(), WhereValue :: string() | number()} | string()],
    Order :: [] | {OrderField :: string(), AscDesc :: string()},
    Limit :: integer(),
    Result :: query_result().

%% @usage:
%% db_mysql:db_select(?DB_TYPE_GAME, rank).
%% db_mysql:db_select(?DB_TYPE_GAME, rank, ["id"]).
%% db_mysql:db_select(?DB_TYPE_GAME, rank, ["id"], [{"id", 1}]).
%% db_mysql:db_select(?DB_TYPE_GAME, rank, ["id"], [{"id", 1}], {"id", "ASC"}).
%% db_mysql:db_select(?DB_TYPE_GAME, rank, ["id"], [{"id", 1}], {"id", "ASC"}, 2).
%% 简单的where xx = xx的格式可以使用 [{WhereKey, WhereValue}]的形式
%% 复杂的where语句就自己写完整的where语句
%% exam:
%% WhereString = lists:flatten(io_lib:format("where id > ~p", [1])),
%% db_mysql:db_select(rank, ["id"], WhereString).
db_select(DBType, Table) ->
    db_select(DBType, Table, ?all).
db_select(DBType, Table, Fields) ->
    db_select(DBType, Table, Fields, "").
db_select(DBType, Table, Fields, Where) ->
    db_select(DBType, Table, Fields, Where, []).
db_select(DBType, Table, Fields, Where, Order) ->
    db_select(DBType, Table, Fields, Where, Order, 0).
db_select(DBType, Table, Fields, Where, Order, Limit) ->
    {Sql, Params} = select_sql_str(Table, Fields, Where, Order, Limit),
%%    ?INFO("Sql ~s~n Params ~p", [Sql, Params]),
    mysql_pool:query(DBType, Sql, Params).

%% @usage:
%% 简单的where xx = xx的格式可以使用 [{WhereKey, WhereValue}]的形式
%% 复杂的where语句就自己写完整的where语句
db_delete(DBType, Table, Where) ->
    {WhereString, Params} = where_string(Where, Table),
    Sql = "DELETE FROM " ++ game_util:to_list(Table) ++ WhereString ++ ";",
    ?LOG_INFO("Sql ~s~n Params ~p", [Sql, Params]),
    mysql_pool:query(DBType, Sql, Params).


%% @usage:
%% db_mysql:db_insert_update(rank, [{id, 1}, {data, "3"}]).
db_insert_update(DBType, Table, Data = [_ | _]) ->
    {InsertSql, Params} = insert_sql_str(Data, Table),
    HeadSql = InsertSql ++ " ON DUPLICATE KEY UPDATE ",
    TailSql = insert_update_sql_tail_str(Data, Table),
    Sql = HeadSql ++ TailSql,
%%    ?INFO("Sql ~s~n Params ~p", [Sql, Params]),
    mysql_pool:query(DBType, Sql, Params).

%%%===================================================================
%%% Private functions
%%%===================================================================
insert_update_sql_tail_str(Data, Table) ->
    insert_update_sql_tail_str(Data, game_util:to_atom(Table), "").
insert_update_sql_tail_str([], _, Acc) ->
    Acc;
insert_update_sql_tail_str([{K, _} | T], Table, "") -> % 第一个不要逗号, 后面的往前插
    insert_update_sql_tail_str(T, Table, io_lib:format("~p.~p=VALUES(~p.~p)", [Table, K, Table, K]));
insert_update_sql_tail_str([{K, _} | T], Table, Acc) ->
    Add = lists:flatten(io_lib:format("~p.~p=VALUES(~p.~p), ", [Table, K, Table, K])),
    insert_update_sql_tail_str(T, Table, Add ++ Acc).

insert_sql_str(Data, Table) ->
    insert_sql_str(lists:reverse(Data), game_util:to_atom(Table), "", "", []).
insert_sql_str([], Table, AccK, AccV, Params) ->
    Head = lists:flatten(io_lib:format("INSERT INTO ~p", [Table])),
    KString = lists:concat([" (", AccK, ") "]),
    VString = lists:concat([" (", AccV, ")"]),
    {Head ++ KString ++ "VALUES" ++ VString, Params};
insert_sql_str([{K, V} | T], Table, "", "", []) -> % 第一个不要逗号, 后面的往前插
    insert_sql_str(T, Table, io_lib:format("~p", [K]), "?", [encode(Table, K, V)]);
insert_sql_str([{K, V} | T], Table, AccK, AccV, Params) ->
    AddK = io_lib:format("~p, ", [K]),
    insert_sql_str(T, Table, AddK ++ AccK, "?, " ++ AccV, [encode(Table, K, V)|Params]).

select_sql_str(Table0, Fields0, Where, Order, Limit) ->
    Table = game_util:to_atom(Table0),
    LimitString = ?IF(Limit == 0, "", " LIMIT " ++ game_util:to_list(Limit)),
    Fields = ?IF(Fields0 == ?all, "*", Fields0),
    case Order of
        [] ->
            OrderString = LimitString;
        {OrderField, AscDesc} ->
            OrderString = " ORDER BY `" ++ game_util:to_list(OrderField) ++ "` " ++ game_util:to_list(AscDesc) ++ LimitString
    end,
    {WhereString0, Params} = where_string(Where, Table),
    WhereString = WhereString0 ++ OrderString,
    FieldsString = lists:flatten(lists:join(",", Fields)) ++ lists:flatten(io_lib:format(" FROM ~p ", [Table])),
    FinalString = "SELECT " ++ FieldsString ++ WhereString,
    {FinalString, Params}.

where_string("", _Table) ->
    {"", []};
where_string(Where, Table) ->
    where_string(Where, Table, "", []).
where_string([], _Table, Tail, Params) ->
    Head = " WHERE ",
    {Head ++ Tail, lists:reverse(Params)};
where_string([{K, V} | T], Table, "", []) -> % 第一个不要逗号, 后面的往前插
    Acc = lists:flatten(io_lib:format("~p = ?", [game_util:to_atom(K)])),
    where_string(T, Table, Acc, [encode(Table, K, V)]);
where_string([{K, V} | T], Table, Acc, Params) ->
    Add = lists:flatten(io_lib:format("~p = ?,", [game_util:to_atom(K)])),
    where_string(T, Table, Add ++ Acc, [encode(Table, K, V)|Params]);
where_string(ComplexWhereString, _Table, _Acc, Params) -> % 不匹配元组列表判断为复杂where语句
    {ComplexWhereString, Params}.


%% @doc 根据数据类型进行编码成参数
encode(Table, K, V) ->
    case is_data_binary(Table, K) of
        ?true ->
            Bin = term_to_binary(V),
            zlib:compress(Bin);
        _ ->
            V
    end.

%% @doc 根据数据类型进行编码成参数
decode(Type, DbData) ->
    case is_data_type_binary(Type) of
        ?true ->
            DbData1 = zlib:uncompress(DbData),
            binary_to_term(DbData1);
        _ ->
            DbData
    end.


%% @doc 是否字段为二进制数据
is_data_binary(Table, Filed0) ->
    Filed = game_util:to_atom(Filed0),
    DbMod = get_db_mod(Table),
    RecFields = DbMod:mod_db_fields(),
    case lists:keyfind(Filed, 1, RecFields) of
        {_, _, Type} ->
            is_data_type_binary(Type);
        Any ->
            ?LOG_ERROR("Field ~p not find in db_mod ~p ~n ~p", [Filed, DbMod, Any]),
            ?false
    end.


%% @doc 是否某种数据类型的值为二进制数据
%% 插入数据报错时查看这块
is_data_type_binary(?TYPE_TINY_BLOB) ->
    ?true;
is_data_type_binary(?TYPE_MEDIUM_BLOB) ->
    ?true;
is_data_type_binary(?TYPE_LONG_BLOB) ->
    ?true;
is_data_type_binary(?TYPE_BLOB) ->
    ?true;
is_data_type_binary(_) ->
    ?false.
